package com.mycat.common.util.country;

import java.io.*;
import java.sql.*;
import java.util.UUID;

/**
 * 初始化国家、省份、市、区到数据库
 * 国家统计局地址   http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/
 *
 * @author roger
 * @email 190642964@qq.com
 * @create 2017-07-27 18:05
 **/
public class InitMain {

    public static void main(String[] args) throws Exception {
        InitMain main = new InitMain();
//        main.createUUID(getUUID());
        main.createAutoIncrement();
   }


    public static String getUUID(){
        String uuid = UUID.randomUUID().toString(); //获取UUID并转化为String对象
        uuid = uuid.replace("-", "");               //因为UUID本身为32位只是生成时多了“-”，所以将它们去点就可
        return uuid;
    }

    public void createUUID(String countryUUID) throws Exception {
        String url = "jdbc:mysql://localhost:3306/swingqq?Unicode=true&characterEncoding=UTF-8";
        String driver = "com.mysql.jdbc.Driver";
        String user = "root";
        String password = "123456";

        String path = "D:\\work\\workspace\\idea\\myCat\\myCat-common\\src\\main\\java\\com\\mycat\\common\\util\\country\\20170310行政区域.txt";
        InputStreamReader isr = new InputStreamReader(new FileInputStream(path));
        BufferedReader br = new BufferedReader(isr);
        String s = null;

        Class.forName("com.mysql.jdbc.Driver");
        // 获取链接
        Connection connection = DriverManager.getConnection(url, user, password);
        s = br.readLine();
        String provinceId = "";
        String cityId = "";
        while (s != null) {

            String two = s.substring(0, 1);
            String four = s.substring(0, 2);

            if (four.equals("　　")) {
                String[] district = s.split(" ");
                String sql = "insert into sys_district (id,code,name,cityId) values(?,?,?,?)";
                PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, getUUID());
                pstmt.setString(2, district[0]);
                pstmt.setString(3, district[district.length - 1]);
                pstmt.setString(4, cityId);
                pstmt.executeUpdate();
            } else if (two.equals("　")) {
                String[] city = s.split(" ");
                cityId = getUUID();
                String sql = "insert into sys_city (id,code,name,provinceId) values(?,?,?,?)";
                PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, cityId);
                pstmt.setString(2, city[0]);
                pstmt.setString(3, city[city.length - 1]);
                pstmt.setString(4, provinceId);
                pstmt.executeUpdate();
            } else {
                String[] province = s.split(" ");
                provinceId = getUUID();
                String sql = "insert into sys_province (id,code,name,countryId) values(?,?,?,?)";
                PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, provinceId);
                pstmt.setString(2, province[0]);
                pstmt.setString(3, province[province.length - 1]);
                pstmt.setString(4, countryUUID);
                pstmt.executeUpdate();
            }
            s = br.readLine();
        }
        br.close();
    }

    public void createAutoIncrement() throws Exception {
        String url = "jdbc:mysql://localhost:3306/swingqq?Unicode=true&characterEncoding=UTF-8";
        String driver = "com.mysql.jdbc.Driver";
        String user = "root";
        String password = "123456";

        String path = "D:\\work\\workspace\\idea\\myCat\\myCat-common\\src\\main\\java\\com\\mycat\\common\\util\\country\\20170310行政区域.txt";
        InputStreamReader isr = new InputStreamReader(new FileInputStream(path));
        BufferedReader br = new BufferedReader(isr);
        String s = null;

        Class.forName("com.mysql.jdbc.Driver");
        // 获取链接
        Connection connection = DriverManager.getConnection(url, user, password);
        s = br.readLine();
        Long provinceId = 0L;
        Long cityId = 0L;
        while (s != null) {

            String two = s.substring(0, 1);
            String four = s.substring(0, 2);

            if (four.equals("　　")) {
                String[] district = s.split(" ");
                String sql = "insert into sys_district (code,name,cityId) values(?,?,?)";
                PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, district[0]);
                pstmt.setString(2, district[district.length - 1]);
                pstmt.setLong(3, cityId);
                pstmt.executeUpdate();
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    Long districtId = rs.getLong(1);
                    System.out.println("        " + districtId + "-" + district[0] + "-" + district[district.length - 1]);
                }
            } else if (two.equals("　")) {
                String[] city = s.split(" ");
                String sql = "insert into sys_city (code,name,provinceId) values(?,?,?)";
                PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, city[0]);
                pstmt.setString(2, city[city.length - 1]);
                pstmt.setLong(3, provinceId);
                pstmt.executeUpdate();
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    cityId = rs.getLong(1);
                    System.out.println("    " + cityId + "-" + city[0] + "-" + city[city.length - 1]);
                }
            } else {
                String[] province = s.split(" ");
                String sql = "insert into sys_province (code,name,countryId) values(?,?,1)";
                PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, province[0]);
                pstmt.setString(2, province[province.length - 1]);
                pstmt.executeUpdate();
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    provinceId = rs.getLong(1);
                    System.out.println(provinceId + "-" + province[0] + "-" + province[province.length - 1]);
                }
            }
            s = br.readLine();
        }
        br.close();
    }
}